# _*_ coding: UTF-8 _*_
# @Time     : 2021/1/25 下午 03:49
# @Author   : Li Jie
# @Site     : http://www.hzdledu.com/
# @File     : sqlite_test.py
# @Software : PyCharm

import sqlite3


# 建表
def sqlite_create_table():
    # 1.连接数据库，如果数据库不存在会创建
    connect = sqlite3.connect('test.db')
    # 也可以创建数据库在内存中
    # connect = sqlite3.connect(':memory')

    # 2.获取游标对象
    cursor = connect.cursor()

    # 3. 执行一条sql语句
    sql = '''Create Table t_students(
    id INT PRIMARY KEY,
    name VARCHAR(20),
    sex VARCHAR(5),
    address VARCHAR(50) 
    );'''
    cursor.execute(sql)

    # 4.提交事务
    connect.commit()

    # 5.关闭游标和连接
    cursor.close()
    connect.close()


# 插入数据
def sqlite_insert_data():
    # 1.连接数据库，如果数据库不存在会创建
    connect = sqlite3.connect('test.db')
    # 也可以创建数据库在内存中
    # connect = sqlite3.connect(':memory')

    # 2.获取游标对象
    cursor = connect.cursor()

    # 3. 执行一条sql语句
    sql1 = 'insert into t_students values (1,"张三","男","成都");'
    sql2 = 'insert into t_students values (2,"李四","女","重庆");'
    sql3 = 'insert into t_students values (3,"王麻子","男","西安");'
    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)

    # 4.提交事务
    connect.commit()

    # 5.关闭游标和连接
    cursor.close()
    connect.close()

def sqlite_select_data():
    # 1.连接数据库，如果数据库不存在会创建
    connect = sqlite3.connect('test.db')
    # 也可以创建数据库在内存中
    # connect = sqlite3.connect(':memory')

    # 2.获取游标对象
    cursor = connect.cursor()

    # 3. 执行一条sql语句
    sql = 'select * from t_students;'
    cursor.execute(sql)

    #4. 获取结果集
    rs = cursor.fetchall()
    print(rs)

    # 5.提交事务
    connect.commit()

    # 6.关闭游标和连接
    cursor.close()
    connect.close()


if __name__ == '__main__':
    # sqlite_create_table()
    # sqlite_insert_data()
    sqlite_select_data()